﻿CREATE FUNCTION [Kernel].[fnSplit]
(
    @StringToSplit VARCHAR(4000),
    @Delimiter VARCHAR(10)
)  
RETURNS @SplittedArray TABLE 
(
    StringPart VARCHAR(100),
    IndexofStringPart INT
)
AS  
BEGIN 
    DECLARE @part VARCHAR(100)
    DECLARE @indexOfDelimiter TINYINT
    DECLARE @indexofPart INT

    SET @part = ''
    SET @indexOfDelimiter = CHARINDEX(@Delimiter, @StringToSplit)
    SET @indexofPart = 1

    IF (@indexOfDelimiter <> 0)
    BEGIN
        WHILE (LEN(@StringToSplit) <> 0)
        BEGIN
            IF (@indexOfDelimiter <> 0)
            BEGIN
                SET @part = SUBSTRING(@StringToSplit, 0, @indexOfDelimiter)
                INSERT INTO @SplittedArray (StringPart, IndexofStringPart) VALUES (@part, @indexOfPart)

                SET @indexofPart = @indexofPart + 1
                SET @StringToSplit = SUBSTRING(@StringToSplit, LEN(@part) + LEN(@Delimiter) + 1, LEN(@StringToSplit))
                SET @indexOfDelimiter = CHARINDEX(@Delimiter, @StringToSplit)
            END
            ELSE
            BEGIN
                INSERT INTO @SplittedArray (StringPart, IndexofStringPart) VALUES (@StringToSplit, @indexOfPart)

                SET @StringToSplit = ''
            END
        END         
    END
    ELSE
    BEGIN
        INSERT INTO @SplittedArray (StringPart, IndexofStringPart) VALUES (@StringToSplit, @indexOfPart)
    END

    RETURN
END
